In [35]:
import pandas as pd
import intake_cs109b_data_mma as cs109b_data
from pandas_profiling import ProfileReport
import matplotlib.pyplot as plt
from matplotlib_venn import venn2
from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot
init_notebook_mode()
import plotly.express as px
from ipywidgets import widgets
import plotly.graph_objects as go
from plotly import figure_factory as ff
import numpy as np
import plotly.io as pio
import seaborn as sns
# pio.renderers.keys()
# pio.renderers.default = 'jupyterlab'
import matplotlib.pylab as plt
In [2]:
df = cs109b_data.original_data.read()
df.head()
Out[2]:
In [3]:
df.groupby("PID")["Latest Budget Changes"].transform("count")
Out[3]:
In [4]:
#assume data is sorted by date
df_unique = df.copy()
df_unique["num_budget_changes"] = df_unique.groupby("PID")["Latest Budget Changes"].transform("count")
df_unique["num_schedule_changes"] = df_unique.groupby("PID")["Latest Schedule Changes"].transform("count")
df_unique["Latest Budget Changes"] = df_unique["Latest Budget Changes"].fillna(0)
#some projects has latest budget changes as first row so need to calculate the original budget
df_unique = df_unique.eval("budget_original = `Latest Budget Changes` + `Budget Forecast`").eval("schedule_original= @pd.to_timedelta(`Latest Schedule Changes`, unit='days') + `Forecast Completion`").drop_duplicates("PID").drop(columns = ["Latest Budget Changes","Latest Schedule Changes"])
df_unique = df_unique.eval("budget_actual = budget_original + `Total Budget Changes`").eval("schedule_actual= schedule_original + @pd.to_timedelta(`Total Schedule Changes`, unit='days')")
df_unique
Out[4]:
Not all of projects start with design phase
In [5]:
df_unique.query("`Current Phase` == 'Design'").PID.nunique()
Out[5]:
- Absolute percentage error: |Forecast − Actual|/Actual
- Relative percentage error: (referred to as cost escalation in project estimation literature): |Forecast − Actual|/Forecast:
- Ratios: Forecast/Actual or Actual/Forecast Difference: Actual − Forecast
- Days delayed (only the positive differences) Absolute error: |Forecast − Actual| Squared error: (Forecast − Actual)2
Calculating metrics¶
In [6]:
df_unique = df_unique.eval('budget_abs_per_error = abs(budget_original - budget_actual)/budget_actual')\
.eval('budget_rel_per_error = abs(budget_original - budget_actual)/budget_original')\
.eval('budget_ratios = budget_actual/budget_original')\
.eval('days_changed_in_schedule = schedule_actual - schedule_original')
df_unique
Out[6]:
In [7]:
(df_unique["Total Schedule Changes"] - df_unique["days_changed_in_schedule"].dt.days)[(df_unique["Total Schedule Changes"] - df_unique["days_changed_in_schedule"].dt.days).notnull()].sum()
Out[7]:
In [8]:
df_unique.PID.nunique()
Out[8]:
In [9]:
df.PID.nunique()
Out[9]:
In [10]:
df.Category.nunique()
Out[10]:
In [11]:
df.info()
In [12]:
df.describe()
Out[12]:
In [13]:
df.columns
Out[13]:
In [14]:
profile = ProfileReport(df, title='Pandas Profiling Report', html={'style':{'full_width':True}})
In [15]:
df.drop_duplicates("Project Name").Description.value_counts()
Out[15]:
Overview¶
Project Name and Description is not a 1 to 1 match, some projects have more than 1 description (?)
Multiple projects can be designed at the same day (10% of projects overlap)
1 Project has project ID but no name
Reports rise steadily from 2014-2018. More than half of reports are in 2019
28 Projects have same description: "Design and construction of a new school". 3 Design and construction of an addition to an existing school. 3 Design and construction of right-of-way green infrastructure in order for the city to comply with NYS DEC consent order which states that NYC must manage 1” of rain on 10% of impervious surfaces by 2030 to reduce combined sewer overflows. The others have unique descriptions
Design start looks bimodal, one around 2000 - 2008, other from 2012 - 2020
Budget forecast varies widely. Most small but a couple of very big ones. One negative budget forecast
Some outliers need to remove to visualize budget metrics
Missing¶
- Don't see any missing patterns in the full data, see some clustering of missing patterns in unique projects
- Should see if there's any missing trends (
df_dummy_unique)
Questions¶
- Why isn't the first phase design for all projects?
- How to find initial budget forecast?
- Should total schedule changes be a percentage?
To do¶
- Plot co-occurence matrix of variables
- Plot category
- Plot category vs number of revisions
- Category vs borough
In [16]:
profile.to_widgets()
In [17]:
profile.to_notebook_iframe()
In [18]:
profile.to_file(output_file="Capital_Projects_EDA_An.html")
Unique, each project one line¶
In [19]:
unique_profile = ProfileReport(df_unique, title='Unique Projects Profiling Report', html={'style':{'full_width':True}})
In [20]:
unique_profile.to_widgets()
In [21]:
unique_profile.to_notebook_iframe()
In [22]:
df_unique_dummy = pd.get_dummies(df_unique, dummy_na = True)
df_unique_dummy
Out[22]:
In [23]:
df_unique_no_outliers = df_unique.query("budget_ratios < 40")
In [24]:
px.violin(df_unique, y="budget_ratios")
In [25]:
px.violin(df_unique_no_outliers, y="budget_ratios")
In [26]:
profile_unique_no_outliers = ProfileReport(df_unique_no_outliers, title='Unique no outliers Profiling Report', html={'style':{'full_width':True}})
In [27]:
profile_unique_no_outliers.to_widgets()
In [28]:
profile_unique_no_outliers.to_notebook_iframe()
Own Analysis¶
In [29]:
venn2([set(df.query("`Total Schedule Changes` == 0").PID.unique()), set(df.query("`Total Budget Changes` == 0").PID.unique())], set_labels= ("No schedule changes", "No budget changes"));
Visualizing stationary attributes¶
In [30]:
df_unique_melted = df_unique.melt(id_vars=["PID", "Category", "Borough", "Total Schedule Changes"], value_vars=["budget_abs_per_error", 'budget_rel_per_error', 'budget_ratios'], var_name="metric", value_name="metric_value")
Budget¶
In [31]:
px.histogram(df_unique_melted,x="metric_value", facet_col="metric", color="Category", barmode="overlay" )
In [32]:
#not visualizing nans
px.histogram(df_unique_melted.dropna(),x="metric_value", facet_col="metric", facet_row= "Category", color="Borough", barmode="overlay" , height=4000)
In [33]:
px.histogram(df_unique_melted.dropna(),x="metric_value", facet_col="metric", facet_row= "Borough", color="Category", barmode="overlay" , height=4000)
Schedule¶
In [34]:
px.histogram(df_unique_melted.dropna(),x="Total Schedule Changes", facet_row= "Category", color="Borough", barmode="overlay" , height=4000)
In [36]:
df.columns
Out[36]:
Change in budget through time¶
In [52]:
px.line(df, x='Date Reported As Of', y="Latest Budget Changes", color = "Category", hover_name="PID")
In [49]:
px.line(df.dropna(), x='Date Reported As Of', facet_col ='Managing Agency', facet_row='Client Agency', y="Latest Budget Changes", color = "Category", height=6000)
In [44]:
px.line(df.dropna(), x='Date Reported As Of', facet_row="Category", facet_col="Borough", y="Latest Budget Changes", color = "PID", height=4000)
In [58]:
px.line(df, x='Date Reported As Of', y="Latest Schedule Changes", color = "Category", hover_name="PID")
In [55]:
px.line(df.dropna(), x='Date Reported As Of', facet_row="Client Agency", facet_col="Managing Agency", y="Latest Schedule Changes", color = "Category", height=4000)
In [53]:
px.line(df.dropna(), x='Date Reported As Of', facet_row="Category", facet_col="Borough", y="Latest Schedule Changes", color = "PID", height=4000)
Explore metrics
- Absolute percentage error: |Forecast − Actual|/Actual
- Relative percentage error: (referred to as cost escalation in project estimation literature): |Forecast − Actual|/Forecast:
- Ratios: Forecast/Actual or Actual/Forecast Difference: Actual − Forecast
- Days delayed (only the positive differences) Absolute error: |Forecast − Actual| Squared error: (Forecast − Actual)2
Ideas:¶
- Encode a "role" of each people working on the project (people who they work with, what projects do they work on etc) in a latent representation/ graph
- Clustering the raw features or use latent representation to find "reference" class
- 1 Model text, one model organizational structure (graph), one model RNN of sequence of events